﻿using System;
using System.Collections.Generic;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for CourtesyCallTable
    /// </summary>
    public static class CourtesyCallTable
    {
        public static DataTable GetReminderList(bool bSeeAll)
        {
            DBLib dbl = new DBLib();
            String sxFilter = String.Empty;
            if (!bSeeAll)
            {
                sxFilter = "and ROWNUM < 6";
            }

            String sxQuery =
            @"
            select
                    T_COURTESY_CALL_ID,
                    COMPANY_NAME,
                    NAME,
                    PLANNED_DATE
                from
                    V_ICP_COURTESY_CALL_LIST
                where
                    CALL_STATUS = 'PLANNED'
                and
                    PLANNED_DATE > sysdate
                and
                    PLANNED_DATE < sysdate + 5"
                + sxFilter +
                @"order by
                    PLANNED_DATE asc";
            DataTable dt = dbl.GetDataTable(sxQuery);
            //dbl.CloseConnection();
            return dt;
        }
        public static String GetReminderCounter()
        {
            DBLib dbl = new DBLib();

            String sxQuery =
            @"
            select
                count(*)
            from
                T_COURTESY_CALL
            where
                CALL_STATUS = 'PLANNED'
            and
                PLANNED_DATE > sysdate
            and
                PLANNED_DATE < sysdate + 5";
            String sxReturn = dbl.GetSingleValue(sxQuery).ToString();
            //dbl.CloseConnection();
            return sxReturn;
        }
        public static DateTime GetPlannedDateById(int iCCID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCCID;

            String sxQuery =
            @"
            select
                PLANNED_DATE
            from
                T_COURTESY_CALL
            where
                T_COURTESY_CALL_ID = :prmID";
            DateTime dtmReturn = Convert.ToDateTime(dbl.GetSingleValue(sxQuery, prmID));
            //dbl.CloseConnection();
            return dtmReturn;
        }

        public static int SaveCall(CCData ccData)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.Direction = ParameterDirection.Output;
            prmID.OracleDbType = OracleDbType.Number;

            OracleParameter prmCPID = new OracleParameter();
            prmCPID.ParameterName = "in_CONTACT_PERSON_ID";
            prmCPID.Direction = ParameterDirection.Input;
            prmCPID.OracleDbType = OracleDbType.Number;
            if (ccData.iContactPersonID < 0)
            {
                prmCPID.Value = DBNull.Value;
            }
            else
            {
                prmCPID.Value = ccData.iContactPersonID;
            }

            OracleParameter prmNewCPID = new OracleParameter();
            prmNewCPID.ParameterName = "in_NEW_CONTACT_PERSON_ID";
            prmNewCPID.Direction = ParameterDirection.Input;
            prmNewCPID.OracleDbType = OracleDbType.Number;
            if (ccData.iNewContactPersonID < 0)
            {
                prmNewCPID.Value = DBNull.Value;
            }
            else
            {
                prmNewCPID.Value = ccData.iNewContactPersonID;
            }

            OracleParameter prmPlannedDate = new OracleParameter();
            prmPlannedDate.ParameterName = "in_PLANNED_DATE";
            prmPlannedDate.Direction = ParameterDirection.Input;
            prmPlannedDate.OracleDbType = OracleDbType.Date;
            prmPlannedDate.Value = ccData.dtmPlannedDate;

            OracleParameter prmCallStatus = new OracleParameter();
            prmCallStatus.ParameterName = "in_CALL_STATUS";
            prmCallStatus.Direction = ParameterDirection.Input;
            prmCallStatus.OracleDbType = OracleDbType.VarChar;
            prmCallStatus.Value = ccData.sxCallStatus;

            OracleParameter prmCreateBy = new OracleParameter();
            prmCreateBy.ParameterName = "in_CREATE_BY";
            prmCreateBy.Direction = ParameterDirection.Input;
            prmCreateBy.OracleDbType = OracleDbType.VarChar;
            prmCreateBy.Value = ICPUser.ID;

            String sxQuery = "pr_INSERT_T_COURTESY_CALL";

            dbl.ExecuteProcedure(sxQuery, prmID, prmCPID, prmNewCPID, prmPlannedDate, prmCallStatus, prmCreateBy);

            Cached.CourtesyCallTable chcCC = new Cached.CourtesyCallTable();
            chcCC.RemoveCache();

            ccData.iCCID = Convert.ToInt32(prmID.Value);
            return ccData.iCCID;
            
        }

        public static int SaveCCQuestion(DBLib dbl, CCQuestionData ccqData)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.Direction = ParameterDirection.Output;
            prmID.OracleDbType = OracleDbType.Number;

            OracleParameter prmValue = new OracleParameter();
            prmValue.ParameterName = "in_VALUE";
            prmValue.Direction = ParameterDirection.Input;
            prmValue.OracleDbType = OracleDbType.VarChar;
            prmValue.Value = ccqData.sxValue;

            OracleParameter prmNote = new OracleParameter();
            prmNote.ParameterName = "in_NOTE";
            prmNote.Direction = ParameterDirection.Input;
            prmNote.OracleDbType = OracleDbType.VarChar;
            prmNote.Value = ccqData.sxNote;

            OracleParameter prmCreateBy = new OracleParameter();
            prmCreateBy.ParameterName = "in_CREATE_BY";
            prmCreateBy.Direction = ParameterDirection.Input;
            prmCreateBy.OracleDbType = OracleDbType.VarChar;
            prmCreateBy.Value = ICPUser.ID;

            OracleParameter prmCCID = new OracleParameter();
            prmCCID.ParameterName = "in_T_COURTESY_CALL_ID";
            prmCCID.Direction = ParameterDirection.Input;
            prmCCID.OracleDbType = OracleDbType.Number;
            prmCCID.Value = ccqData.iCCID;

            OracleParameter prmQuestionTypeID = new OracleParameter();
            prmQuestionTypeID.ParameterName = "in_P_QUESTION_TYPE_ID";
            prmQuestionTypeID.Direction = ParameterDirection.Input;
            prmQuestionTypeID.OracleDbType = OracleDbType.Number;
            prmQuestionTypeID.Value = ccqData.iQuestionTypeID;

            String sxQuery = "pr_insert_T_CC_QUESTION";

            dbl.ExecuteProcedure(sxQuery, prmID, prmValue, prmNote, prmCreateBy, prmCCID, prmQuestionTypeID);
            return Convert.ToInt32(prmID.Value);
            
        }

        public static void CopyCCQuestion2CustInfo(DBLib dbl, int iCustomerID, CCQuestionData ccqData)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ccqData.sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CUSTOMER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_QUESTION_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ccqData.iQuestionTypeID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_UPDATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_COPY_CCQUESTION2CUSTINFO";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            //dbl.CloseConnection();
        }

        public static void EditPlannedDate(int iCCID, DateTime dtmPlannedDate)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            //prmID.Direction = ParameterDirection.Input;
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCCID;

            OracleParameter prmPlannedDate = new OracleParameter();
            prmPlannedDate.ParameterName = "prmPlannedDate";
            //prmPlannedDate.Direction = ParameterDirection.Input;
            prmPlannedDate.OracleDbType = OracleDbType.Date;
            prmPlannedDate.Value = dtmPlannedDate;

            String sxQuery =
            @"
            update
                T_COURTESY_CALL
            set
                PLANNED_DATE = :prmPlannedDate
            where
                T_COURTESY_CALL_ID = :prmID
             ";

            dbl.ExecuteNonQuery(sxQuery, prmPlannedDate, prmID);
            //dbl.CloseConnection();
        }

        public static void CancelCall(int iCCID, String sxReason)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCCID;

            OracleParameter prmReason = new OracleParameter();
            prmReason.ParameterName = "prmReason";
            prmReason.OracleDbType = OracleDbType.VarChar;
            prmReason.Value = sxReason;

            OracleParameter prmUpdateBy = new OracleParameter();
            prmUpdateBy.ParameterName = "prmUpdateBy";
            prmUpdateBy.OracleDbType = OracleDbType.VarChar;
            prmUpdateBy.Value = ICPUser.ID;

            String sxQuery =
            @"
            update
                T_COURTESY_CALL
            set
                CANCELATION_REASON = :prmReason,
                CALL_STATUS = 'CANCELED',
                UPDATE_DATE = sysdate,
                UPDATE_BY = :prmUpdateBy
            where
                T_COURTESY_CALL_ID = :prmID
             ";

            dbl.ExecuteNonQuery(sxQuery, prmReason, prmUpdateBy, prmID);
            //dbl.CloseConnection();
        }
        public static void UpdateCallData(DBLib dbl, CCData ccData)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = ccData.iCCID;

            OracleParameter prmCallStatus = new OracleParameter();
            prmCallStatus.ParameterName = "prmCallStatus";
            prmCallStatus.OracleDbType = OracleDbType.VarChar;
            prmCallStatus.Value = "REALIZED";

            OracleParameter prmUpdateBy = new OracleParameter();
            prmUpdateBy.ParameterName = "prmUpdateBy";
            prmUpdateBy.OracleDbType = OracleDbType.VarChar;
            prmUpdateBy.Value = ICPUser.ID;

            OracleParameter prmNote = new OracleParameter();
            prmNote.ParameterName = "prmNote";
            prmNote.OracleDbType = OracleDbType.VarChar;
            if (ccData.sxNote.Equals(String.Empty) || ccData.sxNote == null)
            {
                prmNote.Value = DBNull.Value;
            }
            else
            {
                prmNote.Value = ccData.sxNote;
            }


            OracleParameter prmRealizationDate = new OracleParameter();
            prmRealizationDate.ParameterName = "prmRealizationDate";
            prmRealizationDate.OracleDbType = OracleDbType.Date;
            prmRealizationDate.Value = ccData.dtmRealizationDate;

            OracleParameter prmFileUploadId = new OracleParameter();
            prmFileUploadId.ParameterName = "prmFileUploadId";
            prmFileUploadId.OracleDbType = OracleDbType.Number;
            if (ccData.iFileUploadId > 0)
            {
                prmFileUploadId.Value = ccData.iFileUploadId;
            }
            else
            {
                prmFileUploadId.Value = DBNull.Value;
            }

            String sxQuery =
            @"
            update
                T_COURTESY_CALL
            set
                CALL_STATUS = :prmCallStatus,
                UPDATE_BY = :prmUpdateBy,
                UPDATE_DATE = sysdate,
                REALIZATION_DATE = :prmRealizationDate,
                NOTE = :prmNote,
                T_FILE_UPLOAD_ID = :prmFileUploadId
            where
                T_COURTESY_CALL_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmCallStatus, prmUpdateBy, prmRealizationDate, prmNote, prmFileUploadId, prmID);
            //dbl.CloseConnection();
        }        
    }
}